package com.face.sql.jsu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.face.bean.jsu.PrestoreName;
import com.megvii.cloud.mylibrary.R.color;
import com.megvii.cloud.mylibrary.R.integer;

public class PreDao {

	Connection con;
	ResultSet  rs;
	PreparedStatement pstmt;
	
	
	public void insert(String username, String phone_num, String name) {
		String sql="insert into prestore_name(username,phone_num,name) values(?,?,?)";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, phone_num);
			pstmt.setString(3, name);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}
	public boolean IsExist(String username, String phone_num, String name) {
		String sql = "SELECT COUNT(*) FROM prestore_name where username=? and phone_num=? and name=?";
		int num=0;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, phone_num);
			pstmt.setString(3, name);
			rs = pstmt.executeQuery();
			while(rs.next()){
				num=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		if(num==0){
			return false;
		}else{
			return true;
		}
		
	}
	public void add(int id,String string, String filepath) {
		
		String sql="insert into prestore_picture(id,phone_num,filepath) values(?,?,?)";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setString(2, string);
			pstmt.setString(3, filepath);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	public List<String> selectByPhoneNum(String phone_num) {
		String sql = "select filepath from prestore_picture where phone_num = "+phone_num;
		List<String> list = new ArrayList<>();
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				list.add(rs.getString("filepath"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return list;
		
	}
	public List<PrestoreName> getPre(String username) {
		String sql = "select id,name,phone_num from prestore_name where username="+username;
		List<PrestoreName> list = new ArrayList<>();
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				PrestoreName prestoreName = new PrestoreName();
				prestoreName.setId(rs.getInt("id"));
				prestoreName.setName(rs.getString("name"));
				prestoreName.setPhone_num(rs.getString("phone_num"));
				list.add(prestoreName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return list;
	}
	public void deletePreById(int id) {
		String sql = "delete from prestore_name where id=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	public void update(int id,String phone_num, String name) {
		String sql = "update prestore_name set name=?,phone_num=? where id=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, phone_num);
			pstmt.setInt(3, id);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}
	public int selectId(String username, String phone_num) {
		int id = 0;
		String sql = "select id from prestore_name where username=? and phone_num=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, phone_num);
			rs = pstmt.executeQuery();
			rs.next();
			id = rs.getInt("id");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return id;
	}
	
	public void deleteById(int id) {
		String sql = "delete from prestore_picture where id=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	
	public List<String> selectFilepathById(int id) {
		List<String> list = new ArrayList<>();
		String sql = "select filepath from prestore_picture where id=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				list.add(rs.getString("filepath"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return list;
	}
	
	public void updatePicture(int id, String phone_num) {
		String sql = "update prestore_picture set phone_num=? where id=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, phone_num);
			pstmt.setInt(2, id);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}
	

}
